<?php
/*--------------------------------------------------------------
   customer_registration_date_migration.php 2022-09-01
   Gambio GmbH
   http://www.gambio.de
   Copyright (c) 2022 Gambio GmbH
   Released under the GNU General Public License (Version 2)
   [http://www.gnu.org/licenses/gpl-2.0.html]
 -------------------------------------------------------------*/

/**
 * setting customers_date_added for main admin to installation date of the shop
 */
$shopInstallationQuery = 'SELECT installation_date FROM `version_history` WHERE is_full_version = 1';
$shopInstallationDate  = $this->query($shopInstallationQuery)[0]['installation_date'];
$shopAdminUpdateQuery  = sprintf('UPDATE `customers` SET `customers_date_added`="%s" WHERE customers_id = 1 AND `customers_date_added`="1000-01-01 00:00:00"',
                                 $shopInstallationDate);
$t_success             &= $this->query($shopAdminUpdateQuery, true);

/**
 * updating customers_date_added to their first order if the date is 1000-01-01...
 */
$firstOrderQuery = <<<SQL
SELECT MIN(`o`.`date_purchased`) as 'first_order', `c`.`customers_id`
FROM `customers` `c`
INNER JOIN `orders` `o` ON `o`.`customers_id`=`c`.`customers_id`
WHERE `c`.`customers_date_added` = '1000-01-01 00:00:00'
GROUP BY `o`.`date_purchased`, `c`.`customers_id`
SQL;

$firstOrderResult = $this->query($firstOrderQuery, true);

if ($firstOrderResult->num_rows > 0) {
    
    while ($row = $firstOrderResult->fetch_assoc()) {
        
        ['first_order' => $firstOrder, 'customers_id' => $id] = $row;
        
        $updateCustomerQuery = <<<SQL
        UPDATE customers SET `customers_date_added` = "%s" WHERE `customers_id`=%s
        SQL;
        $updateCustomerQuery = sprintf($updateCustomerQuery, $firstOrder, $id);
    
        $t_success &= $this->query($updateCustomerQuery, true);
    }
}